﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Cashier_DAO;
using System.Data;
using Cashier_DAO.db;
using Cashier_Model;

namespace Cashier_Service
{
    public class ReportService
    {
        /// <summary>
        /// 合并统计出货和退货结果
        /// </summary>
        /// <param name="memberno"></param>
        /// <param name="years"></param>
        /// <param name="months"></param>
        /// <param name="days"></param>
        /// <param name="lastLimitYears"></param>
        /// <param name="groupfield"></param>
        /// <param name="orderfield"></param>
        /// <returns></returns>
        public DataTable MergerReportShipment(String memberno, int years, int months, int days, int lastLimitYears,
            String groupfield, String orderfield)
        {
            DataTable shipmentDt = this.ReportBillShipment(memberno, years, months, days, lastLimitYears, groupfield, orderfield);
            if (shipmentDt != null)
            {
                DataTable reshipmentDt = this.ReportBillReshipment(memberno, years, months, days, lastLimitYears, groupfield, orderfield);
                shipmentDt.Columns.Add("retotal", System.Type.GetType("System.Decimal"));
                String p_years = "", p_months = "", p_days = "";
                String filter = "";
                foreach (DataRow row in shipmentDt.Rows)
                {
                    p_years = row["years"].ToString();
                    p_months = row["months"].ToString();
                    p_days = row["days"].ToString();

                    filter = "years='" + p_years + "'";
                    if (years > 0)
                    {
                        filter += " and months='" + p_months + "'";
                    }
                    if (months > 0)
                    {
                        filter += " and days='" + p_days + "'";
                    }

                    DataRow[] rowArr = reshipmentDt.Select(filter);
                    if (rowArr != null && rowArr.Length > 0)
                    {
                        row["retotal"] = rowArr[0]["result"];
                    }
                    filter = "";
                }
            }
            return shipmentDt;
        }

        /// <summary>
        /// 统计销售出货报表
        /// </summary>
        /// <param name="memberno">用户编号</param>
        /// <param name="years">年份</param>
        /// <param name="months">月份</param>
        /// <param name="days">月份中某天</param>
        /// <param name="lastLimitYears">最近几年的数据</param>
        /// <param name="groupfield">分组字段名称</param>
        /// <param name="orderfield">排序字段</param>
        /// <returns></returns>
        public DataTable ReportBillShipment(String memberno, int years, int months, int days, int lastLimitYears, 
            String groupfield, String orderfield)
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportParams report = new ReportParams();
                    report.MemberNo = memberno;
                    report.Years = years;
                    report.Months = months;
                    report.Days = days;
                    report.LastLimtYears = lastLimitYears;
                    report.Groupfield = groupfield;
                    report.Orderfield = orderfield;
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.ShipmentReport_SQLite(conn, report);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.ShipmentReport_MySQL(conn, report);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 统计销售出货报表
        /// </summary>
        /// <param name="reportParams"></param>
        /// <returns></returns>
        public DataTable ReportBillShipment(ReportParams reportParams)
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.ShipmentReport_SQLite(conn, reportParams);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.ShipmentReport_MySQL(conn, reportParams);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 获取销售出货统计数据中所有年份
        /// </summary>
        /// <returns></returns>
        public DataTable GetShipmentReportAllYears()
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.GetShipmentReportAllYears_SQLite(conn);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.GetShipmentReportAllYears_MySQL(conn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 统计销售退货报表
        /// </summary>
        /// <param name="memberno">用户编号</param>
        /// <param name="years">年份</param>
        /// <param name="months">月份</param>
        /// <param name="days">月份中某天</param>
        /// <param name="funcname">最近几年</param>
        /// <param name="groupfield">分组字段名称</param>
        /// <param name="orderfield">排序字段</param>
        /// <returns></returns>
        public DataTable ReportBillReshipment(String memberno, int years, int months, int days, int lastLimitYears, 
            String groupfield, String orderfield)
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportParams report = new ReportParams();
                    report.MemberNo = memberno;
                    report.Years = years;
                    report.Months = months;
                    report.Days = days;
                    report.LastLimtYears = lastLimitYears;
                    report.Groupfield = groupfield;
                    report.Orderfield = orderfield;
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.ReshipmentReport_SQLite(conn, report);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.ReshipmentReport_MySQL(conn, report);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 统计采购进货报表
        /// </summary>
        /// <param name="years">年份</param>
        /// <param name="months">月份</param>
        /// <param name="days">月份中某天</param>
        /// <param name="lastLimitYears">最近几年的数据</param>
        /// <param name="groupfield">分组字段名称</param>
        /// <param name="orderfield">排序字段</param>
        /// <returns></returns>
        public DataTable ReportBillPurchase(int years, int months, int days, int lastLimitYears, String groupfield, String orderfield)
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportParams report = new ReportParams();
                    report.Years = years;
                    report.Months = months;
                    report.Days = days;
                    report.LastLimtYears = lastLimitYears;
                    report.Groupfield = groupfield;
                    report.Orderfield = orderfield;
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.PurchaseReport_SQLite(conn, report);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.PurchaseReport_MySQL(conn, report);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 获取采购进货统计数据中所有年份
        /// </summary>
        /// <returns></returns>
        public DataTable GetPurchaseReportAllYears()
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.GetPurchaseReportAllYears_SQLite(conn);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.GetPurchaseReportAllYears_MySQL(conn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 会员消费统计
        /// </summary>
        /// <param name="pageindex">起始页码</param>
        /// <param name="pagesize">页的大小</param>
        /// <param name="memberno">会员编号</param>
        /// <param name="years">年</param>
        /// <param name="months">月</param>
        /// <param name="days">日</param>
        /// <param name="groupfield">分组字段</param>
        /// <param name="orderfield">排序字段</param>
        /// <returns></returns>
        public DataTable MemberTakeReport(int pageindex, int pagesize, String memberno, int years, int months, int days,
            String groupfield, String orderfield)
        {
            DataTable dt = null;
            try
            {
                using (DbConnection conn = ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    ReportParams report = new ReportParams();
                    report.PageIndex = pageindex;
                    report.PageSize = pagesize;
                    report.MemberNo = memberno;
                    report.Years = years;
                    report.Months = months;
                    report.Days = days;
                    report.Groupfield = groupfield;
                    report.Orderfield = orderfield;
                    ReportDAO reportDAO = new ReportDAO();
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        dt = reportDAO.MemberTakeReport_SQLite(conn, report);
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        dt = reportDAO.MemberTakeReport_MySQL(conn, report);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
